/**
 * 版权所有 2009-2015 中国中医科学院中医药信息研究所 何前锋 frontpeak@yahoo.com.cn
 **/
package cn.ac.cintcm.sh.dao;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServlet;

/**
 * 
 * @author xujing 09-04-07
 *
 */
public class HsqldbDao extends HttpServlet {
	
	/**
     * 
     */
    private static final long serialVersionUID = 6718231075440733785L;
    private String ClassString;
	private String ConnectionString;
	private String UserName;
	private String PassWord;
	private Connection Conn;
	private Statement Stmt;
    public static String path;
 
	public HsqldbDao(){
	}
	
	/**
	 * 初始化连接参数
	 *
	 */
	public void initiate(){
		this.ClassString="org.hsqldb.jdbcDriver";
        String classname = this.getClass().getName();
        String classname_resource = "/" + classname.replace('.', '/') + ".class";
        URL url = this.getClass().getResource(classname_resource);
        String urlPath = url.getPath();
        String p = urlPath.substring(0, urlPath.length() - classname_resource.length());
        int index = p.lastIndexOf("/");
        p = p.substring(0,index);
        path = p;
        String paths = p + "/data/herbdb";
        //System.out.println(paths);

		this.ConnectionString="jdbc:hsqldb:" + paths;
		this.UserName="sa";
		this.PassWord="";
	}
	
	/**
	 * 设定连接参数
	 * @param Class
	 * @param Connection
	 * @param UserName
	 * @param PassWord
	 */
	public void setConnection(String Class,
			String Connection,String UserName,String PassWord){
		this.ClassString=Class;
		this.ConnectionString=Connection;
		this.UserName=UserName;
		this.PassWord=PassWord;
	}
	
	/**
	 * 开启连接
	 * @return boolean 
	 */
	public boolean openConnection(){
	   boolean mResult=true;
	   try{
		   Class.forName(this.ClassString);
		   if ((this.UserName==null) && (this.PassWord==null)){
			   this.Conn= DriverManager.getConnection(this.ConnectionString);
		   }
		   else{
			   this.Conn= DriverManager.getConnection(this.ConnectionString,
					   this.UserName,this.PassWord);
		   }
		   this.Stmt=this.Conn.createStatement();
		   mResult=true;
	   }catch(Exception e){
		   System.out.println("Can't find org.hsqldb.jdbcDriver!!");
		   System.out.println(e.toString());
		   mResult=false;
	   }
	   return mResult;
	}
    
    public boolean openConnections(){
        return true;
    }
    
    public void closeConnections(){
        
    }
	
	/**
	 * 关闭连接
	 *
	 */
	public void closeConnection(){
	   try{
		   this.Stmt.close();
		   this.Conn.close();
	   }catch(Exception e){
		   System.out.println(e.toString());
	   }	
	}
	
	/**
	 * 返回查询结果集
	 * @param SqlString
	 * @return ResultSet
	 */
	public ResultSet executeQuery(String SqlString){
		ResultSet result=null;
	    try{
	    	result=this.Stmt.executeQuery(SqlString);
	    }catch(Exception e){
	    	System.out.println(e.toString());
	    }
	    return result;
	}
	
	/**
	 * 返回查询结果集 key模型
	 * @param SqlString
	 * @return ResultSet
	 */
	public int executeInsertKey(String SqlString){
		ResultSet result=null;
		int key = -1;
	    try{

	    	this.Stmt.executeUpdate(SqlString);
	    	
	    	ResultSet resultSet =  this.Stmt.executeQuery("CALL IDENTITY();");
            if(resultSet.next())
            {
                 key = resultSet.getInt(1);
            } else {
	    		System.out.println(this.getClass()+">>插入数据错误");
	    	 //throw new Exception();
	    	}
	    }catch(Exception e){
	    	e.printStackTrace();
	    	System.out.println(e.toString());
	    }
	    return key;
	}
    
    public void execute(String SqlString){
            try {
                Stmt.executeQuery(SqlString);
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
	
	/**
	 * 返回数据库更新结果
	 * @param SqlString
	 * @return
	 */
	public int executeUpdate(String SqlString){
		int result=0;
	    try{
	    	result=this.Stmt.executeUpdate(SqlString);
	    }catch(Exception e){
	    	System.out.println(e.toString() + SqlString);
	    }
	    return result; 
	}
	
    public static void main(String[] args) {
       //HsqldbDao a=new  HsqldbDao();
       //String sql="insert into chapter values(1,'asd')";
       //int p=a.executeUpdate(sql);
       /*try{
    	   while(p.next()){
    		   System.out.println(p.getString("id")+"\t"+p.getShort("name"));
           }
       }catch(Exception w){
    	   w.printStackTrace();
       }*/
    }

    public HsqldbDao getDao() {
        HsqldbDao dao = null;
        dao = new HsqldbDao();
        dao.initiate();
        return dao;
    }

}